﻿using Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
  public  class Index_DAL
    {
        #region RPT绑定
        public List<Index_Model> ProductInfo()
        {
            string sql = @"select * from Products inner join Category on Products.Products_CategoryID=Category.Category_ID";
            SqlDataReader sdr = DBHelper.ExecuteReader(sql);
            List<Index_Model> indexs = new List<Index_Model>();
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    Index_Model index = new Index_Model();
                    index.Products_ID = Convert.ToInt32(sdr["Products_ID"]);
                    index.Products_Name = sdr["Products_Name"].ToString();
                    index.Products_price = Convert.ToInt32(sdr["Products_price"]);
                    index.Products_Remark = sdr["Products_Remark"].ToString();
                    index.Products_imgUrl1 = sdr["Products_imgUrl1"].ToString();
                    index.Products_imgUrl2 = sdr["Products_imgUrl2"].ToString();
                    index.Products_imgUrl3 = sdr["Products_imgUrl3"].ToString();
                    index.Products_imgUrl4 = sdr["Products_imgUrl4"].ToString();
                    index.Products_State = Convert.ToInt32(sdr["Products_State"]);
                    index.Products_CategoryID = Convert.ToInt32(sdr["Products_CategoryID"]); 
                    index.Category_Name = sdr["Category_Name"].ToString();
                    indexs.Add(index);

                }
            }
            return indexs;

        }

        #endregion
        #region 限时特价
      
        public List<Index_Model> ProductInfospecial()
        {
            string sql = @"select top 5 * from Products inner join Category on Products.Products_CategoryID=Category.Category_ID";
            SqlDataReader sdr = DBHelper.ExecuteReader(sql);
            List<Index_Model> indexs = new List<Index_Model>();
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    Index_Model index = new Index_Model();
                    index.Products_ID = Convert.ToInt32(sdr["Products_ID"]);
                    index.Products_Name = sdr["Products_Name"].ToString();
                    index.Products_price = Convert.ToInt32(sdr["Products_price"]);
                    index.Products_Remark = sdr["Products_Remark"].ToString();
                    index.Products_imgUrl1 = sdr["Products_imgUrl1"].ToString();
                    index.Products_imgUrl2 = sdr["Products_imgUrl2"].ToString();
                    index.Products_imgUrl3 = sdr["Products_imgUrl3"].ToString();
                    index.Products_imgUrl4 = sdr["Products_imgUrl4"].ToString();
                    index.Products_State = Convert.ToInt32(sdr["Products_State"]);
                    index.Products_CategoryID = Convert.ToInt32(sdr["Products_CategoryID"]);
                    index.Category_Name = sdr["Category_Name"].ToString();
                    indexs.Add(index);

                }
            }
            return indexs;

        }


        #endregion

        #region 特价优品1
        public List<Index_Model> ProductInfospecial2()
        {
            string sql = @"select top 3 * from Products inner join Category on Products.Products_CategoryID=Category.Category_ID";
            SqlDataReader sdr = DBHelper.ExecuteReader(sql);
            List<Index_Model> indexs = new List<Index_Model>();
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    Index_Model index = new Index_Model();
                    index.Products_ID = Convert.ToInt32(sdr["Products_ID"]);
                    index.Products_Name = sdr["Products_Name"].ToString();
                    index.Products_price = Convert.ToInt32(sdr["Products_price"]);
                    index.Products_Remark = sdr["Products_Remark"].ToString();
                    index.Products_imgUrl1 = sdr["Products_imgUrl1"].ToString();
                    index.Products_imgUrl2 = sdr["Products_imgUrl2"].ToString();
                    index.Products_imgUrl3 = sdr["Products_imgUrl3"].ToString();
                    index.Products_imgUrl4 = sdr["Products_imgUrl4"].ToString();
                    index.Products_State = Convert.ToInt32(sdr["Products_State"]);
                    index.Products_CategoryID = Convert.ToInt32(sdr["Products_CategoryID"]);
                    index.Category_Name = sdr["Category_Name"].ToString();
                    indexs.Add(index);

                }
            }
            return indexs;

        }
        #endregion
        
        #region 特价优品2
            public List<Index_Model> ProductInfospecial3()
        {
            string sql = @"select top 3 * from Products inner join Category on Products.Products_CategoryID=Category.Category_ID order by Products_ID  DESC";
            SqlDataReader sdr = DBHelper.ExecuteReader(sql);
            List<Index_Model> indexs = new List<Index_Model>();
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    Index_Model index = new Index_Model();
                    index.Products_ID = Convert.ToInt32(sdr["Products_ID"]);
                    index.Products_Name = sdr["Products_Name"].ToString();
                    index.Products_price = Convert.ToInt32(sdr["Products_price"]);
                    index.Products_Remark = sdr["Products_Remark"].ToString();
                    index.Products_imgUrl1 = sdr["Products_imgUrl1"].ToString();
                    index.Products_imgUrl2 = sdr["Products_imgUrl2"].ToString();
                    index.Products_imgUrl3 = sdr["Products_imgUrl3"].ToString();
                    index.Products_imgUrl4 = sdr["Products_imgUrl4"].ToString();
                    index.Products_State = Convert.ToInt32(sdr["Products_State"]);
                    index.Products_CategoryID = Convert.ToInt32(sdr["Products_CategoryID"]);
                    index.Category_Name = sdr["Category_Name"].ToString();
                    indexs.Add(index);

                }
            }
            return indexs;

        }
        #endregion

        #region 通过RPT查询的ProductID查询出单条信息

        public Index_Model GetProductinfo(int ProductId)
        {
            string sql = "select  * from Products inner join Category on Products.Products_CategoryID=Category.Category_ID where Products_ID=@Products_ID ";

            SqlParameter[] para =
                 {
                new SqlParameter("@Products_ID",ProductId)
            };
            Index_Model index_Model = new Index_Model();
            SqlDataReader sdr = DBHelper.ExecuteReader(sql, para);
            if(sdr.HasRows)
            {
                while(sdr.Read())
                {
                    index_Model.Products_ID = Convert.ToInt32(sdr["Products_ID"]);
                    index_Model.Products_Name = sdr["Products_Name"].ToString();                    
                    index_Model.Products_imgUrl1 = sdr["Products_imgUrl1"].ToString();                    
                    index_Model.Products_price = Convert.ToInt32(sdr["Products_price"]);
                    
                }
            }
            return index_Model;
        }
        #endregion

        #region 通过model类添加购物车
        public int ShoppingCartinsert(Index_Model index_Model)
        {
            string sql = "insert into ShoppingCart values(@Products_Name,@Products_imgUrl1,1,1,@Products_price)";
            SqlParameter[] parr =
               {
            new SqlParameter ("@Products_Name",index_Model.Products_Name),
             new SqlParameter ("@Products_imgUrl1",index_Model.Products_imgUrl1),
              new SqlParameter ("@Products_price",index_Model.Products_price),

            };
            int i = DBHelper.ExecuteNonQuery(sql, parr);
            return i;
        }
        #endregion
    }
}
